using System;
using System.Collections.Generic;
using System.Text;
using emp.IDAL;
using System.Collections;
using emp.Model;
using System.Data.SqlClient;
using System.Data;
using emp.Utility;

namespace emp.SqlServerDAL
{
    public class QunaireQuest : IQunaireQuest
    {


        private const string SQL_SELECT_LIST = " SELECT QQT_QUR_UID,QQT_QSD_UID, isnull(QQT_ORDER,''),isnull(QQT_CAN_MODIFY,'N'),isnull(QQT_IS_NULL,'Y'), isnull(QSD_NAME,''),isnull(QSD_TYPE,'T'),isnull(qqt_qst_type,''),isnull(qqt_max_len,'') FROM QUESTIONNAIRE_QUEST LEFT OUTER JOIN QUEST_STD ON QQT_QSD_UID = QSD_UID where QQT_QUR_UID = @Quniare_ID ";
        private const string SQL_SELECT_QUESTSTR = " SELECT QQT_QSD_UID FROM QUESTIONNAIRE_QUEST INNER JOIN QUEST_STD ON QQT_QSD_UID=QSD_UID where QQT_QUR_UID = @Quniare_ID order by QQT_ORDER ";

        private const string SQL_COUNT_QUEST_NUM = " SELECT COUNT(*) FROM QUESTIONNAIRE_QUEST where QQT_QUR_ID = @Quniare_ID";

        //private const string SQL_SELECT_QUESTIONINFO_LIST = " SELECT QQT_QUR_ID, QQT_QSD_ID, QSD_NAME, QQT_ORDER, QQT_CAN_MODIFY,QQT_IS_NULL FROM QUESTIONNAIRE_QUEST LEFT OUTER JOIN QUEST_STD ON QQT_QSD_ID = QSD_ID where QQT_QUR_ID = @Quniare_ID ";

        private const string PARM_QUNAIRE_ID = "@Quniare_ID";

        #region common
        public int Insert(QunaireQuestInfo qustionInfo)
        {

            string sQuestionIDStr = qustionInfo.QuestionIDStr.Trim();
            
            string[] qustionArray = sQuestionIDStr.Split(',');

            string sSqlInsert = " delete from questionnaire_quest where QQT_QUR_UID = " + qustionInfo.QunaireID.ToString().Trim() + ";";

            int i = 0;
            foreach (string squstionID in qustionArray)
            {
                i++;
               // sSqlInsert += " insert into questionnaire_quest(QQT_QUR_UID,QQT_QSD_UID,QQT_ORDER )  values(" + qustionInfo.QunaireID.ToString().Trim() + "," + squstionID + "," + i + ");";
                sSqlInsert += " insert into questionnaire_quest(QQT_QUR_UID,QQT_QSD_UID,QQT_ORDER ,qqt_max_len)  select " + qustionInfo.QunaireID.ToString().Trim() + "," + squstionID + "," + i + " , qsd_length from QUEST_STD where qsd_uid =" + squstionID+";";
            }

            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlInsert, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.QunaireQuest.Insert()";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue;
        }

        public int Update(IList questList)
        {
            string sSqlUpdate = "";

            for (int index = 0; index < questList.Count; index++)
            {
                QunaireQuestInfo qustionInfo = (QunaireQuestInfo)questList[index];
                sSqlUpdate += " update questionnaire_quest set qqt_order=" + qustionInfo.QuestionOrder.Trim() + ",qqt_can_modify = '" +
                    qustionInfo.CanModify.Trim() + "',qqt_is_null = '" + qustionInfo.IsNull.Trim() +
                    "',qqt_qst_type= '" + qustionInfo.QQT_QST_TYPE + "',qqt_max_len=" + qustionInfo.QQT_MAX_LEN;
                sSqlUpdate += " where qqt_qur_uid = " + qustionInfo.QunaireID + "  and qqt_qsd_uid = " + qustionInfo.QuestionID + ";";
            }

            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlUpdate, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.QunaireQuest.Update()";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue;
        }

        public IList getQunaireQuestList(int iQustionnaireID)
        {
            IList qustionList = new ArrayList();

            SqlParameter sqlParm = new SqlParameter(PARM_QUNAIRE_ID, SqlDbType.Int, 4, "QQT_QUR_ID");
            sqlParm.Value = iQustionnaireID;
            try
            {
                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_LIST, sqlParm))
                {
                    while (rdr.Read())
                    {
                        QunaireQuestInfo qustionInfo = new QunaireQuestInfo();
                        qustionInfo.QunaireID = rdr.GetInt32(0);
                        qustionInfo.QuestionID = rdr.GetInt32(1);
                        qustionInfo.QuestionOrder = rdr.GetInt32(2).ToString();
                        qustionInfo.CanModify = rdr.GetString(3);
                        qustionInfo.IsNull = rdr.GetString(4);
                        qustionInfo.QuestionName = rdr.GetString(5);
                        qustionInfo.Qunaire_Type = rdr.GetString(6);
                        qustionInfo.QQT_QST_TYPE = rdr.GetString(7);
                        qustionInfo.QQT_MAX_LEN = rdr.GetInt32(8);

                        qustionList.Add(qustionInfo);
                    }
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.QunaireQuest.getQunaireQuestList()";
                EventsLog.WriteLog(err, sMethod);
            }
            return qustionList;
        }

        public string getQunaireQuestIDStr(int iQustionnaireID)
        {
            string sQuestIDStr = "";

            SqlParameter sqlParm = new SqlParameter(PARM_QUNAIRE_ID, SqlDbType.Int, 4, "QQT_QUR_ID");
            sqlParm.Value = iQustionnaireID;
            try
            {
                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_QUESTSTR, sqlParm))
                {
                    while (rdr.Read())
                    {
                        sQuestIDStr += rdr.GetInt32(0) + ",";
                    }
                }
                if (sQuestIDStr.Length > 0)
                {
                    sQuestIDStr = sQuestIDStr.Substring(0, sQuestIDStr.Length - 1);
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.QunaireQuest.getQunaireQuestIDStr()";
                EventsLog.WriteLog(err, sMethod);
            }

            return sQuestIDStr;
        }

        #endregion

        #region Senior
        public int PartQuestInsert(QuesttionnairePartQuestInfo qpartinfo)
        {
            string sQuestionIDStr = qpartinfo.QSDIDStr.Trim();
           
            string[] qustionArray = sQuestionIDStr.Split(',');            

            string sSqlInsert = " delete from QUESTIONNAIRE_PART_QUEST where QPQ_QUP_ID = " + qpartinfo.QPQ_QUP_ID.ToString().Trim() + ";";

            int i = 0;
            foreach (string squstionID in qustionArray)
            {
                i++;
                sSqlInsert += " insert into QUESTIONNAIRE_PART_QUEST values(" + qpartinfo.QPQ_QUP_ID.ToString().Trim() + "," + squstionID + ","+i+",'Y',null,null,'N',null,'N','Y');";
            }

            sSqlInsert += "update QUESTIONNAIRE_PART_QUEST set QPQ_MAX_LEN=isnull(QSD_LENGTH,'0') from "
                + "QUEST_STD where QSD_UID=QUESTIONNAIRE_PART_QUEST.QPQ_QSD_ID and QUESTIONNAIRE_PART_QUEST.QPQ_QUP_ID = " + qpartinfo.QPQ_QUP_ID.ToString().Trim() + ";";
               

            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlInsert, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.QunairePartQuest.Insert()";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue;
        }

        public int PartQuestLabelInsert(QuesttionnairePartQuestInfo qpartinfo)
        {            
            string sLabelIDStr = qpartinfo.QPQ_IS_LABEL.Trim();
            /*
            if (string.IsNullOrEmpty(sQuestionIDStr))
            {
                return 0;
            }
            */
           
            string[] labelArray = sLabelIDStr.Split(',');

            string sSqlInsert = "";

            sSqlInsert = " delete from QUESTIONNAIRE_PART_QUEST where  QPQ_IS_LABEL='Y'and QPQ_QUP_ID = " + qpartinfo.QPQ_QUP_ID.ToString().Trim() + ";";

            foreach (string slabelID in labelArray)
            {
                sSqlInsert += " insert into QUESTIONNAIRE_PART_QUEST values(" + qpartinfo.QPQ_QUP_ID.ToString().Trim() + "," + slabelID + ",null,null,null,null,null,null,'Y','Y');";
            }

            //sSqlInsert += "update QUESTIONNAIRE_PART_QUEST";

            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlInsert, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.QunairePartQuest.Insert()";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue; 
        }

        public QuestionairetPartInfo getPartInfo(int part_id)
        {
            string SQL_STR = "SELECT QUP_ID,QUP_QUR_ID,ISNULL(QUP_NAME,''),ISNULL(QUP_DESC,''),QUP_ORDER,QUP_SHOW FROM QUESTIONNAIRE_PARTS WHERE QUP_ID=" + part_id;
            QuestionairetPartInfo qpartinfo = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    qpartinfo = new QuestionairetPartInfo();
                    qpartinfo.QUP_ID = rdr.GetInt32(0);
                    qpartinfo.QUP_QUR_ID = rdr.GetInt32(1);
                    qpartinfo.QUP_NAME = rdr.GetString(2);
                    qpartinfo.QUP_DESC = rdr.GetString(3);
                    qpartinfo.QUP_ORDER = rdr.GetInt32(4);
                    qpartinfo.QUP_SHOW = rdr.GetString(5);
                }
            }
            return qpartinfo;
        }

        public string getQuestPartQsdIDStr(int part_id)
        {
            string sQuestIDStr = "";
            string SQL_STR = "SELECT QPQ_QSD_ID FROM QUESTIONNAIRE_PART_QUEST INNER JOIN QUEST_STD ON QPQ_QSD_ID=QSD_UID WHERE ISNULL(QPQ_IS_LABEL,'N')='N'"
                + " AND QPQ_QUP_ID=" + part_id + " ORDER BY QSD_SORT_ORDER";

            try
            {
                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
                {
                    while (rdr.Read())
                    {
                        sQuestIDStr += rdr.GetInt32(0) + ",";
                    }
                }
                if (sQuestIDStr.Length > 0)
                {
                    sQuestIDStr = sQuestIDStr.Substring(0, sQuestIDStr.Length - 1);
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.QunaireQuest.getQunaireQuestIDStr()";
                EventsLog.WriteLog(err, sMethod);
            }

            return sQuestIDStr;
        }

        public string getQuestPartIDByQur_id(int qur_id,string type)
        {
            string sQuestIDStr = "";
          //  string SQL_STR = "SELECT QUP_ID FROM QUESTIONNAIRE_PARTS WHERE QUP_ID IN (SELECT QPQ_QUP_ID FROM QUESTIONNAIRE_PART_QUEST  where  qpq_is_view  is  null  or qpq_is_view!='2') AND QUP_QUR_ID=" + qur_id + " ORDER BY QUP_ORDER";
            string SQL_STR = "SELECT QUP_ID FROM QUESTIONNAIRE_PARTS WHERE QUP_ID IN (SELECT QPQ_QUP_ID FROM QUESTIONNAIRE_PART_QUEST  where    qpq_is_view  is  null  or qpq_is_view!='N') AND QUP_QUR_ID=" + qur_id + " ORDER BY QUP_ORDER";

            try
            {
                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
                {
                    while (rdr.Read())
                    {
                        sQuestIDStr += rdr.GetInt32(0) + ",";
                    }
                }
                if (sQuestIDStr.Length > 0)
                {
                    sQuestIDStr = sQuestIDStr.Substring(0, sQuestIDStr.Length - 1);
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.QunaireQuest.getQunaireQuestIDStr()";
                EventsLog.WriteLog(err, sMethod);
            }

            return sQuestIDStr;
        }
        public string getQuestPartIDByQur_id(int qur_id)
        {
            string sQuestIDStr = "";
            string SQL_STR = "SELECT QUP_ID FROM QUESTIONNAIRE_PARTS WHERE QUP_ID IN (SELECT QPQ_QUP_ID FROM QUESTIONNAIRE_PART_QUEST) AND QUP_QUR_ID=" + qur_id + " ORDER BY QUP_ORDER";

            try
            {
                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
                {
                    while (rdr.Read())
                    {
                        sQuestIDStr += rdr.GetInt32(0) + ",";
                    }
                }
                if (sQuestIDStr.Length > 0)
                {
                    sQuestIDStr = sQuestIDStr.Substring(0, sQuestIDStr.Length - 1);
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.QunaireQuest.getQunaireQuestIDStr()";
                EventsLog.WriteLog(err, sMethod);
            }

            return sQuestIDStr;
        }

        public IList getQPartQuestInfo(int part_id, int qur_id,string type)
        {
            string SQL_STR = "SELECT QPQ_ID,QPQ_QUP_ID,QPQ_QSD_ID,ISNULL(QPQ_IS_NULL,''),ISNULL(QPQ_QST_TYPE,''),"
                + "ISNULL(QPQ_MAX_LEN,0),ISNULL(QPQ.QPQ_ORDER,0),ISNULL(QSD.QSD_NAME,''),ISNULL(QPQ_IS_SWITCH,'N'),ISNULL(QSD_TYPE,''),ISNULL(QPQ_IS_LABEL,'N'),ISNULL(QPQ_RELATE,'N'),isnull(QPQ_IS_VIEW,'') "
                + " FROM QUESTIONNAIRE_PART_QUEST QPQ LEFT JOIN QUEST_STD QSD ON QPQ.QPQ_QSD_ID= QSD.QSD_UID WHERE QPQ_QUP_ID=" + part_id;
            SQL_STR += " AND QPQ_QUP_ID IN (SELECT QUP_ID FROM QUESTIONNAIRE_PARTS WHERE QUP_QUR_ID=" + qur_id + ") and QPQ_IS_VIEW ='Y' ORDER BY ISNULL(QPQ_ORDER,0)";
            IList qpartquestlist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    QuesttionnairePartQuestInfo qpartinfo = new QuesttionnairePartQuestInfo();
                    qpartinfo.QPQ_ID = rdr.GetInt32(0);
                    qpartinfo.QPQ_QUP_ID = rdr.GetInt32(1);
                    qpartinfo.QPQ_QSD_ID = rdr.GetInt32(2);
                    qpartinfo.QPQ_IS_NULL = rdr.GetString(3);
                    qpartinfo.QPQ_QST_TYPE = rdr.GetString(4);
                    qpartinfo.QPQ_MAX_LEN = rdr.GetInt32(5);
                    qpartinfo.QPQ_ORDER = rdr.GetInt32(6);
                    qpartinfo.QPQ_QSD_NAME = rdr.GetString(7);
                    qpartinfo.QPQ_IS_SWITCH = rdr.GetString(8);
                    qpartinfo.QUEST_TYPE = rdr.GetString(9);
                    qpartinfo.QPQ_IS_LABEL = rdr.GetString(10);
                    qpartinfo.QPQ_RELATE = rdr.GetString(11);
                    qpartinfo.QPQ_IS_VIEW = rdr.GetString(12);
                    qpartquestlist.Add(qpartinfo);
                }
            }
            return qpartquestlist;
        }
        public IList getQPartQuestInfo(int part_id,int qur_id)
        {
            string SQL_STR = "SELECT QPQ_ID,QPQ_QUP_ID,QPQ_QSD_ID,ISNULL(QPQ_IS_NULL,''),ISNULL(QPQ_QST_TYPE,''),"
                + "ISNULL(QPQ_MAX_LEN,0),ISNULL(QPQ.QPQ_ORDER,0),ISNULL(QSD.QSD_NAME,''),ISNULL(QPQ_IS_SWITCH,'N'),ISNULL(QSD_TYPE,''),ISNULL(QPQ_IS_LABEL,'N'),ISNULL(QPQ_RELATE,'N'),isnull(QPQ_IS_VIEW,'') "
                + " FROM QUESTIONNAIRE_PART_QUEST QPQ LEFT JOIN QUEST_STD QSD ON QPQ.QPQ_QSD_ID= QSD.QSD_UID WHERE QPQ_QUP_ID=" + part_id;
            SQL_STR += " AND QPQ_QUP_ID IN (SELECT QUP_ID FROM QUESTIONNAIRE_PARTS WHERE QUP_QUR_ID=" + qur_id + ") ORDER BY ISNULL(QPQ_ORDER,0)";
            IList qpartquestlist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    QuesttionnairePartQuestInfo qpartinfo = new QuesttionnairePartQuestInfo();
                    qpartinfo.QPQ_ID = rdr.GetInt32(0);
                    qpartinfo.QPQ_QUP_ID = rdr.GetInt32(1);
                    qpartinfo.QPQ_QSD_ID = rdr.GetInt32(2);
                    qpartinfo.QPQ_IS_NULL = rdr.GetString(3);
                    qpartinfo.QPQ_QST_TYPE = rdr.GetString(4);
                    qpartinfo.QPQ_MAX_LEN = rdr.GetInt32(5);
                    qpartinfo.QPQ_ORDER = rdr.GetInt32(6);
                    qpartinfo.QPQ_QSD_NAME = rdr.GetString(7);
                    qpartinfo.QPQ_IS_SWITCH = rdr.GetString(8);
                    qpartinfo.QUEST_TYPE = rdr.GetString(9);
                    qpartinfo.QPQ_IS_LABEL = rdr.GetString(10);
                    qpartinfo.QPQ_RELATE = rdr.GetString(11);
                    qpartinfo.QPQ_IS_VIEW = rdr.GetString(12);
                    qpartquestlist.Add(qpartinfo);
                }
            }
            return qpartquestlist;
        }

        public int UpdatePartQuest(IList PartQuestList)
        {
            string sSqlUpdate = "";
            int returnValue = 0;
            for (int index = 0; index < PartQuestList.Count; index++)
            {
                QuesttionnairePartQuestInfo qustionInfo = (QuesttionnairePartQuestInfo)PartQuestList[index];
                sSqlUpdate += " update QUESTIONNAIRE_PART_QUEST set QPQ_ORDER=" + qustionInfo.QPQ_ORDER + ",QPQ_IS_NULL = '" + qustionInfo.QPQ_IS_NULL +
                    "',QPQ_QST_TYPE= '" + qustionInfo.QPQ_QST_TYPE + "',QPQ_MAX_LEN=" + qustionInfo.QPQ_MAX_LEN + ",QPQ_RELATE='" + qustionInfo.QPQ_RELATE + "',qpq_is_view='" + qustionInfo.QPQ_IS_VIEW+ "'";
                sSqlUpdate += " where QPQ_ID = " + qustionInfo.QPQ_ID + ";";
            }

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlUpdate, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.QunairePartQuest.Update()";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue;
        }

        public int PartSwitchDeal(IList qspinfolist, int regionpartid)
        {
            string SQL_STR = "DELETE FROM QUEST_SWITCH_PARTS WHERE QSP_QPQ_ID "
                + "IN (SELECT QPQ_ID FROM QUESTIONNAIRE_PART_QUEST WHERE QPQ_QUP_ID=" + regionpartid + ");";
            int ReturnValue = 0;
            for (int k = 0; k < qspinfolist.Count; k++)
            {
                QuestSwitchPartInfo apswitchinfo = ((QuestSwitchPartInfo)qspinfolist[k]);
                SQL_STR += "INSERT INTO QUEST_SWITCH_PARTS(QSP_QPQ_ID,QSP_ANSWER_VALUE,QSP_QUP_ID) "
                + "VALUES(" + apswitchinfo.QSP_QPQ_ID + ",'" + apswitchinfo.QSP_ANSWER_VALUE + "','" + apswitchinfo.QSP_QUP_ID + "');";
            }

            SQL_STR += "UPDATE QUESTIONNAIRE_PART_QUEST SET QPQ_IS_SWITCH='N' WHERE QPQ_QUP_ID = " + regionpartid + ";";
            SQL_STR += "UPDATE QUESTIONNAIRE_PART_QUEST SET QPQ_IS_SWITCH='Y' ,qpq_is_view='Y' WHERE QPQ_ID = " + ((QuestSwitchPartInfo)qspinfolist[0]).QSP_QPQ_ID + ";";

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        ReturnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_STR, null);
                        trans.Commit();
                    }
                    catch (Exception err)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            return ReturnValue;
        }

        public QuestSwitchPartInfo getQSPartInfo(int qpartid)
        {
            string SQL_STR = "SELECT QSP_ID,QSP_QPQ_ID,ISNULL(QSP_ANSWER_VALUE,''),QSP_QUP_ID FROM QUEST_SWITCH_PARTS "
                + "WHERE QSP_QPQ_ID=" + qpartid;
            //SqlParameter[] param = new SqlParameter[] { };
            QuestSwitchPartInfo pspinfo = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    pspinfo = new QuestSwitchPartInfo();
                    pspinfo.QSP_ID = rdr.GetInt32(0);
                    pspinfo.QSP_QPQ_ID = rdr.GetInt32(1);
                    pspinfo.QSP_ANSWER_VALUE = rdr.GetString(2);
                    pspinfo.QSP_QUP_ID = rdr.GetInt32(3);
                }
            }

            return pspinfo;
        }

        public int getSwitchPartId(int qpqid, string avalue)
        {
            string SQL_STR = "SELECT QSP_QUP_ID FROM QUEST_SWITCH_PARTS "
                + "WHERE QSP_ANSWER_VALUE='" + avalue + "' and QSP_QPQ_ID=" + qpqid + "";
            int partid = 0;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    partid = rdr.GetInt32(0);
                }
            }
            return partid;

        }

        public IList getSwitchList(int qpqid)
        {
            string SQL_STR = "SELECT QSP_ID,QSP_QPQ_ID,ISNULL(QSP_ANSWER_VALUE,''),QSP_QUP_ID FROM QUEST_SWITCH_PARTS WHERE QSP_QPQ_ID="+qpqid;
            IList switchlist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    QuestSwitchPartInfo qspartinfo = new QuestSwitchPartInfo();
                    qspartinfo.QSP_ID = rdr.GetInt32(0);
                    qspartinfo.QSP_QPQ_ID = rdr.GetInt32(1);
                    qspartinfo.QSP_ANSWER_VALUE = rdr.GetString(2);
                    qspartinfo.QSP_QUP_ID = rdr.GetInt32(3);
                    switchlist.Add(qspartinfo);
                }
            }
            return switchlist;
        }

        public bool isrelatequestparent(int qsduid)
        {
            bool ReturnValue = false;
            string SQL_STR = "SELECT 1 FROM QUEST_STD_RELATE WHERE QSR_QSD_UID_PARENT=" + qsduid;
            
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    ReturnValue = true;
                }
            }            
            return ReturnValue;
        }

        public bool isrelatequestchild(int qsduid)
        {
            bool ReturnValue = false;
            string SQL_STR = "SELECT 1 FROM QUEST_STD_RELATE WHERE QSR_QSD_UID_CHILD=" + qsduid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    ReturnValue = true;
                }
            }
            return ReturnValue; 
        }

        public IList getrelatequestlist(int qur_id)
        {
            string SQL_STR = "select qsrd.qsrd_qsa_uid_child,qsrd_qsa_uid_parent,"
                + "qsa1.qsa_answer_name as answer_parent_name,qsa1.qsa_answer_value as answer_parent_value,"
                + "qsa2.qsa_answer_name as answer_child_name,qsa2.qsa_answer_value as answer_child_value,"
                + "qsr.QSR_QSD_UID_PARENT,qsd.QSD_COLUMN_ALIAS from QUEST_STD_RELATE qsr,QUEST_STD_RELATE_DETAIL qsrd,"
                + "QUEST_STD_ANSWER qsa1,QUEST_STD_ANSWER qsa2,QUEST_STD qsd where qsr.qsr_uid = qsrd.qsrd_qsr_uid "
                + "and qsrd.qsrd_qsa_uid_parent = qsa1.qsa_uid and qsrd.qsrd_qsa_uid_child = qsa2.qsa_uid "
                + "and qsd.QSD_UID=qsr.QSR_QSD_UID_CHILD and  qsa1.qsa_status='Y' and  qsa2.qsa_status='Y' "
                + "and qsr.QSR_QSD_UID_PARENT  IN (SELECT QPQ_QSD_ID FROM QUESTIONNAIRE_PART_QUEST QPQ INNER JOIN "
                + "QUESTIONNAIRE_PARTS QP ON QPQ.QPQ_QUP_ID=QP.QUP_ID WHERE QUP_QUR_ID=" + qur_id + ") "
                + "order by qsa1.qsa_answer_name,qsa2.qsa_answer_name";
            IList relatelist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, null))
            {
                while (rdr.Read())
                {
                    string[] relatevalue = new string[8];
                    relatevalue[0] = rdr.GetInt32(0).ToString();
                    relatevalue[1] = rdr.GetInt32(1).ToString();
                    relatevalue[2] = rdr.GetString(2);
                    relatevalue[3] = rdr.GetString(3);
                    relatevalue[4] = rdr.GetString(4);
                    relatevalue[5] = rdr.GetString(5);
                    relatevalue[6] = rdr.GetInt32(6).ToString();
                    relatevalue[7] = rdr.GetString(7);
                    relatelist.Add(relatevalue);
                }
            }
            return relatelist;
        }

        public string getrelatecolumnname(int qsduid)
        {
            string returnvalue = "";
            string sqlstr = "select QSD_COLUMN_ALIAS from QUEST_STD where QSD_UID in (select QSR_QSD_UID_CHILD from QUEST_STD_RELATE where QSR_QSD_UID_PARENT = "+qsduid+")";
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sqlstr, null))
            {
                while (rdr.Read())
                {
                    returnvalue += rdr.GetString(0)+",";                    
                }
            }
            if (returnvalue.Length > 0)
            {
                returnvalue = returnvalue.Remove(returnvalue.Length - 1);
            }
            return returnvalue;
        }
        #endregion
    }


}
